from typing import List, Dict

from openpyxl import Workbook
from openpyxl.cell import Cell
from openpyxl.styles import PatternFill, Alignment, Font, Border, Side
from openpyxl.utils import get_column_letter
from openpyxl.worksheet.worksheet import Worksheet
from pydantic import BaseModel

from .schema import (
    SummaryTable,
    SummaryProduct,
    START_ROW_OF_DATA,
    Offer,
    OfferDetail,
    ExceptionOffer,
    ExceptionOfferDetail,
)

YELLOW_FILL = PatternFill(fill_type="solid", fgColor="FFFF00")
GRAY_FILL = PatternFill(fill_type="solid", fgColor="D3D3D3")
RED_FILL = PatternFill(fill_type="solid", fgColor="FA8072")
ALIGNMENT = Alignment(horizontal="center", vertical="center")
SIDE = Side(border_style="thin", color="000000")
BORDER = Border(top=SIDE, bottom=SIDE, left=SIDE, right=SIDE)


def set_common_style(cell):
    cell.font = Font(name="宋体", size=10, bold=False)
    cell.alignment = ALIGNMENT
    cell.border = BORDER


def set_header_style(cell: Cell):
    set_common_style(cell)
    cell.font = Font(name="黑体", size=10, bold=False)


def set_lowest_price_style(cell: Cell):
    set_common_style(cell)
    cell.fill = YELLOW_FILL


def set_exception_style(cell: Cell, status: int):
    set_common_style(cell)
    if status == -1:
        cell.fill = RED_FILL
    elif status == -2:
        cell.fill = GRAY_FILL


class SupplierColumns(BaseModel):
    supplier: str
    start_col: int
    has_bargaining: bool


class Exporter:
    def __init__(self, tables: List[SummaryTable]):
        self.tables = tables
        self.workbook = Workbook()

    def export(self, file_path: str):
        sheet_names = self._create_worksheets()
        for sheet_name in sheet_names:
            print(f"Processing sheet: {sheet_name}")
            table = next((t for t in self.tables if t.table_name == sheet_name), None)
            if table is None:
                raise ValueError(f"No table found for sheet name: {sheet_name}")
            # self._add_data_in_sheet(sheet=sheet)
            columns_of_suppliers, column_of_lowest_price = self._add_header(
                sheet_name, table
            )
            self._add_data_in_sheet(
                sheet_name, table, columns_of_suppliers, column_of_lowest_price
            )
            self._add_style_in_sheet(
                sheet_name, columns_of_suppliers, column_of_lowest_price
            )
            self._merge_cells_of_duplicate_product_names(sheet_name)

        self.workbook.save(file_path)

    def _create_worksheets(self):
        for table in self.tables:
            self.workbook.create_sheet(title=table.table_name)

        if "Sheet" in self.workbook.sheetnames:
            std = self.workbook["Sheet"]
            self.workbook.remove(std)
        return self.workbook.sheetnames

    def _add_exception_data_in_sheet(
        self,
        sheet_name: str,
        table: SummaryTable,
        supplier_start_columns: Dict[str, SupplierColumns],
    ) -> int:
        sheet = self.workbook[sheet_name]
        row_idx = sheet.max_row + 3

        for exception in table.exceptions:
            detail = exception.detail
            sheet.cell(row=row_idx, column=1, value=detail.order_num)
            sheet.cell(row=row_idx, column=2, value=detail.code)
            sheet.cell(row=row_idx, column=3, value=detail.name)
            sheet.cell(row=row_idx, column=4, value=detail.spec)
            sheet.cell(row=row_idx, column=5, value=detail.unit)
            sheet.cell(row=row_idx, column=6, value=detail.required_brand)

            # 设置异常样式
            for col in range(1, 7):
                set_exception_style(sheet.cell(row=row_idx, column=col), status=-2)

            supplier = exception.offer.supplier
            if supplier in supplier_start_columns:
                config = supplier_start_columns[supplier]
                start_col = config.start_col

                # 获取报价详情（兼容OfferDetail和ExceptionOfferDetail）
                offer_detail = exception.offer.detail

                # 单价列
                price_col = (
                    start_col + 1
                    if exception.offer.is_bargaining and config.has_bargaining
                    else start_col
                )
                sheet.cell(
                    row=row_idx,
                    column=price_col,
                    value=getattr(offer_detail, "unit_price", "异常报价"),
                )

                # 品牌列
                brand_col = start_col + (
                    2 if exception.offer.is_bargaining and config.has_bargaining else 1
                )
                sheet.cell(
                    row=row_idx,
                    column=brand_col,
                    value=getattr(offer_detail, "brand", ""),
                )

                # 备注列（安全获取remark属性）
                remark_col = start_col + (
                    3 if exception.offer.is_bargaining and config.has_bargaining else 2
                )
                remark = ""

                # 从detail获取备注信息（兼容不同detail类型）
                if hasattr(offer_detail, "remark"):
                    remark = getattr(offer_detail, "remark", "")
                elif hasattr(offer_detail, "description"):
                    remark = getattr(offer_detail, "description", "")

                # 添加品牌要求差异信息
                if detail.required_brand:
                    brand = getattr(offer_detail, "brand", "")
                    if brand != detail.required_brand:
                        remark = f"{remark} 要求品牌:{detail.required_brand}".strip()

                sheet.cell(row=row_idx, column=remark_col, value=remark)

                # 设置异常单元格样式
                for offset in range(4 if config.has_bargaining else 3):
                    cell = sheet.cell(row=row_idx, column=start_col + offset)
                    if cell.value is not None:
                        set_exception_style(cell, status=-1)

            row_idx += 1

        return row_idx

    def _merge_cells_of_duplicate_product_names(self, sheet_name: str):
        if sheet_name not in self.workbook:
            raise ValueError(f"Sheet '{sheet_name}' does not exist in the workbook.")

        sheet = self.workbook[sheet_name]
        col_idx_of_product_name = 3  # 产品名称在第3列 (C列)

        current_product_name = None
        occurrence = 0
        start_row_to_merge = None

        for row_idx in range(1, sheet.max_row + 1):
            value = sheet.cell(row=row_idx, column=col_idx_of_product_name).value
            if current_product_name is None:
                current_product_name = value
                occurrence = 1
                start_row_to_merge = row_idx
            elif current_product_name == value:
                occurrence += 1
            else:
                if occurrence >= 2:
                    # 合并单元格
                    sheet.merge_cells(
                        start_row=start_row_to_merge,
                        end_row=row_idx - 1,
                        start_column=col_idx_of_product_name,
                        end_column=col_idx_of_product_name,
                    )
                    # 设置合并后的单元格自动换行
                    cell = sheet.cell(
                        row=start_row_to_merge, column=col_idx_of_product_name
                    )
                    cell.alignment = Alignment(
                        wrap_text=True, vertical="center", horizontal="center"
                    )  # 同时设置垂直居中
                current_product_name = value
                occurrence = 1
                start_row_to_merge = row_idx

        # 处理最后一组重复项
        if occurrence >= 2:
            sheet.merge_cells(
                start_row=start_row_to_merge,
                end_row=row_idx,
                start_column=col_idx_of_product_name,
                end_column=col_idx_of_product_name,
            )
            # 设置合并后的单元格自动换行
            cell = sheet.cell(row=start_row_to_merge, column=col_idx_of_product_name)
            cell.alignment = Alignment(
                wrap_text=True, vertical="center"
            )  # 同时设置垂直居中

        # 可选：调整产品名称列的宽度以适应换行
        sheet.column_dimensions[get_column_letter(col_idx_of_product_name)].width = (
            25  # 设置合适的列宽
        )

    def _add_header(self, sheet_name: str, table: SummaryTable):
        sheet = self.workbook[sheet_name]
        # 新表头结构：序号、产品代码、产品名称、规格型号（带指示灯）、单位、要求品牌
        product_headers = [
            "序号",
            "产品代码",
            "产品名称",
            "规格型号（带指示灯）",
            "单位",
            "要求品牌",
        ]
        column_widths_of_products = [4, 12, 16, 32, 8, 16]  # 调整列宽
        column_widths_of_offer = 16
        target_row_height = 20

        supplier_start_columns = {}
        col_idx = 7  # 从 G 列开始 (A-F 是产品列)

        # --- 1. 设置产品信息列 (A-F) ---
        for i, header in enumerate(product_headers):
            cell = sheet.cell(row=1, column=i + 1, value=header)
            set_header_style(cell)
            sheet.merge_cells(
                start_row=1, end_row=2, start_column=i + 1, end_column=i + 1
            )
            col_letter = get_column_letter(i + 1)
            sheet.column_dimensions[col_letter].width = column_widths_of_products[i]

        # --- 其余代码保持不变... ---

        # --- 2. 为每个供应商设置报价列 ---
        for supplier in table.suppliers:
            start_col = col_idx
            has_bargaining = supplier in table.suppliers_with_bargaining

            supplier_start_columns[supplier] = SupplierColumns(
                supplier=supplier, start_col=start_col, has_bargaining=has_bargaining
            )

            # --- 第1列：单价 ---
            cell = sheet.cell(row=2, column=col_idx, value="单价")
            set_header_style(cell)
            sheet.column_dimensions[
                get_column_letter(col_idx)
            ].width = column_widths_of_offer
            col_idx += 1

            # --- 第2列：二次议价（如果该供应商在 suppliers_with_bargaining 中）---
            if has_bargaining:
                cell = sheet.cell(row=2, column=col_idx, value="二次议价")
                set_header_style(cell)
                sheet.column_dimensions[
                    get_column_letter(col_idx)
                ].width = column_widths_of_offer
                col_idx += 1

            # --- 第3列：品牌 ---
            cell = sheet.cell(row=2, column=col_idx, value="品牌")
            set_header_style(cell)
            sheet.column_dimensions[
                get_column_letter(col_idx)
            ].width = column_widths_of_offer
            col_idx += 1

            # --- 第4列：备注 ---
            cell = sheet.cell(row=2, column=col_idx, value="备注")
            set_header_style(cell)
            sheet.column_dimensions[
                get_column_letter(col_idx)
            ].width = column_widths_of_offer
            col_idx += 1

            # --- 合并第一行的供应商名称 ---
            end_col = col_idx - 1
            sheet.merge_cells(
                start_row=1, end_row=1, start_column=start_col, end_column=end_col
            )
            header_cell = sheet.cell(row=1, column=start_col, value=supplier)
            set_header_style(header_cell)

        # --- 3. 添加 "最低价" 列（中间空一列）---
        col_idx += 1  # 空出一列
        lowest_price_col_idx = col_idx
        cell = sheet.cell(row=1, column=lowest_price_col_idx, value="最低价")
        set_header_style(cell)
        sheet.merge_cells(
            start_row=1,
            end_row=2,
            start_column=lowest_price_col_idx,
            end_column=lowest_price_col_idx,
        )
        sheet.column_dimensions[
            get_column_letter(lowest_price_col_idx)
        ].width = column_widths_of_offer

        # --- 4. 添加每个供应商的 "X-最低价" 列 ---
        for supplier in table.suppliers:
            col_idx += 1
            cell = sheet.cell(row=1, column=col_idx, value=f"{supplier}-最低价")
            set_header_style(cell)
            sheet.merge_cells(
                start_row=1, end_row=2, start_column=col_idx, end_column=col_idx
            )
            sheet.column_dimensions[
                get_column_letter(col_idx)
            ].width = column_widths_of_offer

        # --- 5. 设置行高 ---
        sheet.row_dimensions[1].height = target_row_height
        sheet.row_dimensions[2].height = target_row_height

        return supplier_start_columns, lowest_price_col_idx

    def _add_style_in_sheet(
        self, sheet_name, columns_of_suppliers, column_of_lowest_price
    ):
        sheet = self.workbook[sheet_name]
        for row_idx in range(START_ROW_OF_DATA, sheet.max_row + 1):
            # 设置产品信息列的样式 (A-F)
            for col_idx in range(1, 7):
                cell = sheet.cell(row=row_idx, column=col_idx)
                set_common_style(cell)

            # 设置供应商报价列的样式
            for supplier, config in columns_of_suppliers.items():
                start_col = config.start_col
                # 报价列数：3列（单价、品牌、备注） + （1列“二次议价” if 有议价）
                num_cols = 4 if config.has_bargaining else 3
                for offset in range(num_cols):
                    cell = sheet.cell(row=row_idx, column=start_col + offset)
                    set_common_style(cell)

            # 设置“最低价”和“X-最低价”列的样式
            # 最低价列 + 每个供应商一个“X-最低价”列
            num_diff_cols = len(columns_of_suppliers)
            for offset in range(num_diff_cols + 1):  # +1 是最低价列自己
                cell = sheet.cell(row=row_idx, column=column_of_lowest_price + offset)
                set_common_style(cell)

    def _add_data_in_sheet(
        self,
        sheet_name: str,
        table: SummaryTable,
        supplier_start_columns: Dict[str, SupplierColumns],
        lowest_price_col_idx: int,
    ):
        sheet = self.workbook[sheet_name]
        row_idx = START_ROW_OF_DATA + 1

        for product in table.products:
            # === 1, 2, 3. 填充单元格数据和基础样式（这部分逻辑不变，且是正确的） ===
            # ... (填充产品信息、构建offer_map、填充所有报价单元格的代码与上一版相同，此处省略)
            detail = product.detail
            sheet.cell(row=row_idx, column=1, value=detail.order_num)

            # 处理规格型号显示（合并相同情况）
            if detail.spec == detail.name:
                sheet.cell(row=row_idx, column=3, value=detail.name)
            else:
                sheet.cell(row=row_idx, column=3, value=detail.name)
                sheet.cell(row=row_idx, column=4, value=detail.spec)
            sheet.cell(row=row_idx, column=4, value=detail.unit)
            sheet.cell(row=row_idx, column=6, value=detail.required_brand)

            offer_map = {}
            for offer in product.offers:
                supplier = offer.supplier
                if supplier not in offer_map:
                    offer_map[supplier] = {"initial": None, "bargaining": None}
                if offer.is_bargaining:
                    offer_map[supplier]["bargaining"] = offer
                else:
                    offer_map[supplier]["initial"] = offer

            for supplier, cols_config in supplier_start_columns.items():
                if supplier not in offer_map:
                    continue
                start_col = cols_config.start_col
                offer_data = offer_map.get(supplier, {})
                initial_offer = offer_data.get("initial")
                bargaining_offer = offer_data.get("bargaining")
                if initial_offer:
                    init_cell = sheet.cell(row=row_idx, column=start_col)
                    init_cell.value = initial_offer.detail.unit_price
                    if (
                        isinstance(initial_offer, ExceptionOffer)
                        and initial_offer.status != 0
                    ):
                        set_exception_style(init_cell, status=initial_offer.status)
                if cols_config.has_bargaining and bargaining_offer:
                    bargain_cell = sheet.cell(row=row_idx, column=start_col + 1)
                    bargain_cell.value = bargaining_offer.detail.unit_price
                    if (
                        isinstance(bargaining_offer, ExceptionOffer)
                        and bargaining_offer.status != 0
                    ):
                        set_exception_style(
                            bargain_cell, status=bargaining_offer.status
                        )
                final_offer = bargaining_offer or initial_offer
                if final_offer and final_offer.detail:
                    brand_val = getattr(final_offer.detail, "brand", "")
                    remark_val = getattr(final_offer.detail, "remark", "")
                    brand_col = start_col + (2 if cols_config.has_bargaining else 1)
                    sheet.cell(row=row_idx, column=brand_col, value=brand_val)

            # === 4. 【全新逻辑】确定每个供应商的“最终有效报价”并检查是否存在无法覆盖的严重错误 ===

            # effective_offers_map: 存储每个供应商最终可用于比价的有效报价
            effective_offers_map: Dict[str, Offer] = {}
            # has_unrecoverable_error: 标记该产品行是否存在一个最终的、无法被覆盖的严重错误
            has_unrecoverable_error = False

            # 遍历所有供应商，来确定他们各自的最终有效报价
            for supplier in supplier_start_columns.keys():
                offer_data = offer_map.get(supplier, {})
                initial = offer_data.get("initial")
                bargaining = offer_data.get("bargaining")

                effective_offer = None

                # 规则1: 优先判断议价。如果议价是有效的Offer，它就是最终报价。
                if bargaining and isinstance(bargaining, Offer):
                    effective_offer = bargaining
                # 规则2: 如果议价是严重异常，则该供应商存在一个无法挽回的错误。
                elif (
                    bargaining
                    and isinstance(bargaining, ExceptionOffer)
                    and bargaining.status != 0
                ):
                    has_unrecoverable_error = True
                    break  # 一旦发现一个无法挽回的错误，立即停止检查，整个产品都不能比价。

                # 规则3: 如果议价不存在，或者是status=0的无报价异常，则回溯判断初报价。
                # (仅当effective_offer尚未被赋值时，此逻辑块才会执行)
                if not effective_offer:
                    # 如果初报价是有效的Offer，它就是最终报价。
                    if initial and isinstance(initial, Offer):
                        effective_offer = initial
                    # 如果初报价是严重异常，同样标记为无法挽回的错误。
                    elif (
                        initial
                        and isinstance(initial, ExceptionOffer)
                        and initial.status != 0
                    ):
                        has_unrecoverable_error = True
                        break

                # 如果找到了当前供应商的有效报价，则存入map中
                if effective_offer:
                    effective_offers_map[supplier] = effective_offer

            # === 5. 填写最低价 ===
            if has_unrecoverable_error:
                sheet.cell(
                    row=row_idx,
                    column=lowest_price_col_idx,
                    value="N/A(存在严重异常报价)",
                )
            else:
                # 将筛选出的、绝对有效的报价集合传递给计算函数
                self._add_lowest_and_difference_price(
                    sheet,
                    row_idx,
                    effective_offers_map,  # 传递处理好的有效报价map
                    supplier_start_columns,
                    lowest_price_col_idx,
                )

            row_idx += 1

        # ... (后续代码省略)
        if table.exceptions:
            row_idx = self._add_exception_data_in_sheet(
                sheet_name, table, supplier_start_columns
            )

        return row_idx

    @staticmethod
    def _add_lowest_and_difference_price(
        sheet: Worksheet,
        row_idx: int,
        effective_offers: Dict[str, Offer],  # 直接接收处理好的有效报价
        columns_of_suppliers: Dict[str, SupplierColumns],
        lowest_price_col_idx: int,
    ):
        # === 1. 如果没有任何有效报价，则无法计算 ===
        if not effective_offers:
            sheet.cell(
                row=row_idx, column=lowest_price_col_idx, value="N/A(无有效报价)"
            )
            return

        # === 2. 找到全局最低价（逻辑简化） ===
        min_price = min(offer.key_price for offer in effective_offers.values())

        # === 3. 填充“最低价”列 ===
        lowest_price_cell = sheet.cell(row=row_idx, column=lowest_price_col_idx)
        lowest_price_cell.value = round(min_price, 2)

        # === 4. 填充差值并高亮最低价（逻辑更清晰） ===
        suppliers_with_min_price = []
        for supplier, final_offer in effective_offers.items():
            diff = final_offer.key_price - min_price
            if abs(diff) < 1e-5:
                suppliers_with_min_price.append((supplier, final_offer))

            if supplier not in columns_of_suppliers:
                continue

            # 计算差值列的位置
            diff_col_idx = (
                lowest_price_col_idx
                + list(columns_of_suppliers.keys()).index(supplier)
                + 1
            )
            diff_cell = sheet.cell(row=row_idx, column=diff_col_idx)
            diff_cell.value = round(diff, 2)

        # === 5. 标记最低价单元格 ===
        for supplier, offer in suppliers_with_min_price:
            if supplier not in columns_of_suppliers:
                continue

            config = columns_of_suppliers[supplier]
            start_col = config.start_col

            # 根据最终使用的有效报价是初轮还是议价，来确定高亮哪一列
            target_col = start_col + 1 if offer.is_bargaining else start_col
            target_cell = sheet.cell(row=row_idx, column=target_col)
            set_lowest_price_style(target_cell)
